﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NPSoft.Ferrero.DataBridge.Model;
using NPSoft.Ferrero.Framework.DAL;

namespace NPSoft.Ferrero.DataBridge.DAL
{
   public class DistributorUtil:IDAL<Distributor>
    {
        public int Add(Distributor info)
        {
            throw new NotImplementedException();
        }

        public int Edit(Distributor info)
        {
            throw new NotImplementedException();
        }

        public Distributor GetById(long id)
        {
            throw new NotImplementedException();
        }

        public Distributor GetById(Guid id)
        {
            throw new NotImplementedException();
        }

        public int Remove(Distributor info)
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet Search(StringBuilder condtion)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// SQL连接
        /// </summary>
        private SqlConnection Connection
        {
            get
            {
                return SqlHelper.GetConnection();
            }
        }

        public ICollection<Distributor> SearchCollection(StringBuilder condtion)
        {
            ICollection<Distributor> infos = new List<Distributor>();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT DISTINCT dbo.LeftPad(DIS.DIST_FACT_CD ,'0',10) AS DIST_FACT_CD,
                                DIS.DIST_FACT_NAME ,
                                DIS.DIST_FACT_SHORTNAME ,
                                DIS.DIST_FACT_SHORTNAME_EN ,
                                DIS.DIST_GROUP_CD ,
                                DIS.DIST_GROUP ,
                                DIS.ADDR_1 AS SOLD_ADDR_1 ,
                                DIS.ADDR_2 AS SOLD_ADDR_2 ,
                                DIS.ADDR_3 AS SOLD_ADDR_3 ,
                                DIS.ADDR_4 AS SOLD_ADDR_4 ,
                                DIS.ADDR_5 AS SOLD_ADDR_5 ,
                                DIS.ADDR_POSTAL AS SOLD_ADDR_POSTAL ,
                                DIS.ADDR_CONT_PR AS SOLD_ADDR_CONT_PR ,
                                DIS.ADDR_CONT_NO AS SOLD_ADDR_CONT_NO ,
                                DIS.ADDR_FAX_NO AS SOLD_ADDR_FAX_NO ,
                                DIS.ADDR_E_1 AS SOLD_ADDR_E_1 ,
                                DIS.ADDR_E_2 AS SOLD_ADDR_E_2 ,
                                DIS.ADDR_E_3 AS SOLD_ADDR_E_3 ,
                                DIS.ADDR_E_4 AS SOLD_ADDR_E_4 ,
                                DIS.ADDR_E_5 AS SOLD_ADDR_E_5 ,
                                SOLD_GEO.CITY_DESC_EN AS SOLD_CITY_NODE_DESC ,
                                SOLD_GEO.CITY_DESC_CN AS SOLD_CITY_NODE_CH_DESC ,
                                SOLD_GEO.CC_DESC_EN AS SOLD_Cluster_NODE_DESC ,
                                SOLD_GEO.CC_DESC_CN AS SOLD_Cluster_NODE_CH_DESC ,
                                SOLD_GEO.AREA_DESC_EN AS SOLD_Area_NODE_DESC ,
                                SOLD_GEO.AREA_DESC_CN AS SOLD_Area_NODE_CH_DESC ,
                                SOLD_GEO.RGN_DESC_EN AS SOLD_Region_NODE_DESC ,
                                SOLD_GEO.RGN_DESC_CN AS SOLD_Region_NODE_CH_DESC ,
                                dbo.LeftPad(ADDR.ADDR_CD,'0',10) AS ADDR_CD,
                                ADDR.ADDR_DESC ,
                                ADDR.SHIPTO_SHOTNAME ,
                                ADDR.SHIPTO_E_SHOTNAME ,
                                ADDR.ADDR_1 AS SHIP_ADDR_1 ,
                                ADDR.ADDR_2 AS SHIP_ADDR_2 ,
                                ADDR.ADDR_3 AS SHIP_ADDR_3 ,
                                ADDR.ADDR_4 AS SHIP_ADDR_4 ,
                                ADDR.ADDR_5 AS SHIP_ADDR_5 ,
                                ADDR.ADDR_POSTAL AS SHIP_ADDR_POSTAL ,
                                ADDR.ADDR_CONT_PR AS SHIP_ADDR_CONT_PR ,
                                ADDR.ADDR_CONT_NO AS SHIP_ADDR_CONT_NO ,
                                ADDR.ADDR_FAX_NO AS SHIP_ADDR_FAX_NO ,
                                ADDR.ADDR_E_1 AS SHIP_ADDR_E_1 ,
                                ADDR.ADDR_E_2 AS SHIP_ADDR_E_2 ,
                                ADDR.ADDR_E_3 AS SHIP_ADDR_E_3 ,
                                ADDR.ADDR_E_4 AS SHIP_ADDR_E_4 ,
                                ADDR.ADDR_E_5 AS SHIP_ADDR_E_5 ,
                                ADDR.RENAME_FLAG ,
                                ADDR.RENAME_DT ,
                                CASE DIS.SYNCOPERATION WHEN 'D' THEN '1' ELSE '0' END SOLD_Status,
                                CASE ADDR.SYNCOPERATION WHEN 'D' THEN '1' ELSE '0' END SHIP_Status,
                                '' AS SOLD_Con_Ter,
                                '' AS SHIP_Con_Ter,
                                SHIP_GEO.CITY_DESC_EN AS SHIP_CITY_NODE_DESC ,
                                SHIP_GEO.CITY_DESC_CN AS SHIP_CITY_NODE_CH_DESC ,
                                SHIP_GEO.CC_DESC_EN AS SHIP_Cluster_NODE_DESC ,
                                SHIP_GEO.CC_DESC_CN AS SHIP_Cluster_NODE_CH_DESC ,
                                SHIP_GEO.AREA_DESC_EN AS SHIP_Area_NODE_DESC ,
                                SHIP_GEO.AREA_DESC_CN AS SHIP_Area_NODE_CH_DESC ,
                                SHIP_GEO.RGN_DESC_EN AS SHIP_Region_NODE_DESC ,
                                SHIP_GEO.RGN_DESC_CN AS SHIP_Region_NODE_CH_DESC
                        FROM    MST_DIST_FACT AS DIS
                                LEFT JOIN MST_ADDRESS AS ADDR ON DIS.DIST_FACT_CD = ADDR.ADDR_CD
                                LEFT JOIN UFN_GET_TREEINFO('ST') AS SOLD_GEO ON DIS.CITY_CODE = SOLD_GEO.CITY_CD
                                LEFT JOIN UFN_GET_TREEINFO('ST') AS SHIP_GEO ON ADDR.CITY_CODE = SHIP_GEO.CITY_CD");

            string sWhere = " where ";
            if (!string.IsNullOrEmpty(condtion.ToString()))
            {

                sql.Append(sWhere + condtion);
            }
            DataSet ds;
            ds = SqlHelper.ExecuteDataset(this.Connection, CommandType.Text, sql.ToString());
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                infos.Add(SetInfo(row));
            }
            return infos;
        }

       /// <summary>
       /// 
       /// </summary>
       /// <param name="row"></param>
       /// <returns></returns>
        public Distributor SetInfo(System.Data.DataRow row)
        {
            Distributor distributor = new Distributor();
            distributor.ADDR_CD = SqlHelper.GetString(row, "ADDR_CD");
            distributor.ADDR_DESC = SqlHelper.GetString(row, "ADDR_DESC");
            distributor.DIST_FACT_CD = SqlHelper.GetString(row, "DIST_FACT_CD");
            distributor.DIST_FACT_NAME = SqlHelper.GetString(row, "DIST_FACT_NAME");
            distributor.DIST_FACT_SHORTNAME = SqlHelper.GetString(row, "DIST_FACT_SHORTNAME");
            distributor.DIST_FACT_SHORTNAME_EN = SqlHelper.GetString(row, "DIST_FACT_SHORTNAME_EN");
            distributor.DIST_GROUP = SqlHelper.GetString(row, "DIST_GROUP");
            distributor.DIST_GROUP_CD = SqlHelper.GetString(row, "DIST_GROUP_CD");
            distributor.RENAME_DT = SqlHelper.GetDateTime(row, "RENAME_DT");
            distributor.RENAME_FLAG = SqlHelper.GetString(row, "RENAME_FLAG");
            distributor.SHIP_ADDR_1 = SqlHelper.GetString(row, "SHIP_ADDR_1");
            distributor.SHIP_ADDR_2 = SqlHelper.GetString(row, "SHIP_ADDR_2");
            distributor.SHIP_ADDR_3 = SqlHelper.GetString(row, "SHIP_ADDR_3");
            distributor.SHIP_ADDR_4 = SqlHelper.GetString(row, "SHIP_ADDR_4");
            distributor.SHIP_ADDR_5 = SqlHelper.GetString(row, "SHIP_ADDR_5");
            distributor.SHIP_ADDR_CONT_NO = SqlHelper.GetString(row, "SHIP_ADDR_CONT_NO");
            distributor.SHIP_ADDR_CONT_PR = SqlHelper.GetString(row, "SHIP_ADDR_CONT_PR");
            distributor.SHIP_ADDR_E_1 = SqlHelper.GetString(row, "SHIP_ADDR_E_1");
            distributor.SHIP_ADDR_E_2 = SqlHelper.GetString(row, "SHIP_ADDR_E_2");
            distributor.SHIP_ADDR_E_3 = SqlHelper.GetString(row, "SHIP_ADDR_E_3");
            distributor.SHIP_ADDR_E_4 = SqlHelper.GetString(row, "SHIP_ADDR_E_4");
            distributor.SHIP_ADDR_E_5 = SqlHelper.GetString(row, "SHIP_ADDR_E_5");
            distributor.SHIP_ADDR_FAX_NO = SqlHelper.GetString(row, "SHIP_ADDR_FAX_NO");
            distributor.SHIP_ADDR_POSTAL = SqlHelper.GetString(row, "SHIP_ADDR_POSTAL");
            distributor.SHIP_Area_NODE_CH_DESC = SqlHelper.GetString(row, "SHIP_Area_NODE_CH_DESC");
            distributor.SHIP_Area_NODE_DESC = SqlHelper.GetString(row, "SHIP_Area_NODE_DESC");
            distributor.SHIP_CITY_NODE_CH_DESC = SqlHelper.GetString(row, "SHIP_CITY_NODE_CH_DESC");
            distributor.SHIP_CITY_NODE_DESC = SqlHelper.GetString(row, "SHIP_CITY_NODE_DESC");
            distributor.SHIP_Cluster_NODE_CH_DESC = SqlHelper.GetString(row, "SHIP_Cluster_NODE_CH_DESC");
            distributor.SHIP_Cluster_NODE_DESC = SqlHelper.GetString(row, "SHIP_Cluster_NODE_DESC");
            distributor.SHIP_Region_NODE_CH_DESC = SqlHelper.GetString(row, "SHIP_Region_NODE_CH_DESC");
            distributor.SHIP_Region_NODE_DESC = SqlHelper.GetString(row, "SHIP_Region_NODE_DESC");
            distributor.SHIPTO_E_SHOTNAME = SqlHelper.GetString(row, "SHIPTO_E_SHOTNAME");
            distributor.SHIPTO_SHOTNAME = SqlHelper.GetString(row, "SHIPTO_SHOTNAME");
            distributor.SOLD_ADDR_1 = SqlHelper.GetString(row, "SOLD_ADDR_1");
            distributor.SOLD_ADDR_2 = SqlHelper.GetString(row, "SOLD_ADDR_2");
            distributor.SOLD_ADDR_3 = SqlHelper.GetString(row, "SOLD_ADDR_3");
            distributor.SOLD_ADDR_4 = SqlHelper.GetString(row, "SOLD_ADDR_4");
            distributor.SOLD_ADDR_5 = SqlHelper.GetString(row, "SOLD_ADDR_5");
            distributor.SOLD_ADDR_CONT_NO = SqlHelper.GetString(row, "SOLD_ADDR_CONT_NO");
            distributor.SOLD_ADDR_CONT_PR = SqlHelper.GetString(row, "SOLD_ADDR_CONT_PR");
            distributor.SOLD_ADDR_E_1 = SqlHelper.GetString(row, "SOLD_ADDR_E_1");
            distributor.SOLD_ADDR_E_2 = SqlHelper.GetString(row, "SOLD_ADDR_E_2");
            distributor.SOLD_ADDR_E_3 = SqlHelper.GetString(row, "SOLD_ADDR_E_3");
            distributor.SOLD_ADDR_E_4 = SqlHelper.GetString(row, "SOLD_ADDR_E_4");
            distributor.SOLD_ADDR_E_5 = SqlHelper.GetString(row, "SOLD_ADDR_E_5");
            distributor.SOLD_ADDR_FAX_NO = SqlHelper.GetString(row, "SOLD_ADDR_FAX_NO");
            distributor.SOLD_ADDR_POSTAL = SqlHelper.GetString(row, "SOLD_ADDR_POSTAL");
            distributor.SOLD_Area_NODE_CH_DESC = SqlHelper.GetString(row, "SOLD_Area_NODE_CH_DESC");
            distributor.SOLD_Area_NODE_DESC = SqlHelper.GetString(row, "SOLD_Area_NODE_DESC");
            distributor.SOLD_CITY_NODE_CH_DESC = SqlHelper.GetString(row, "SOLD_CITY_NODE_CH_DESC");
            distributor.SOLD_CITY_NODE_DESC = SqlHelper.GetString(row, "SOLD_CITY_NODE_DESC");
            distributor.SOLD_Cluster_NODE_CH_DESC = SqlHelper.GetString(row, "SOLD_Cluster_NODE_CH_DESC");
            distributor.SOLD_Cluster_NODE_DESC = SqlHelper.GetString(row, "SOLD_Cluster_NODE_DESC");
            distributor.SOLD_Region_NODE_CH_DESC = SqlHelper.GetString(row, "SOLD_Region_NODE_CH_DESC");
            distributor.SOLD_Region_NODE_DESC = SqlHelper.GetString(row, "SOLD_Region_NODE_DESC");
            distributor.SHIP_Status = SqlHelper.GetString(row, "SHIP_Status");
            distributor.SHIP_Con_Ter = SqlHelper.GetString(row, "SHIP_Con_Ter");
            distributor.SOLD_Status = SqlHelper.GetString(row, "SOLD_Status");
            distributor.SOLD_Con_Ter = SqlHelper.GetString(row, "SOLD_Con_Ter");

            return distributor;
        }
    }
}
